package jdbc.base;

import org.junit.BeforeClass;
import org.junit.Test;

import java.sql.*;

/**
 * 项目名称：Stage2.1
 * 类 名 称：Base
 * 类 描 述：TODO
 * 创建时间：2019-12-14 11:01
 */
public class Base {
    @BeforeClass
    public static void beforeClass() {
        //注册驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("注册驱动失败");
        }
    }

    @Test
    public void testCreateDBAndTable() {

        String url = "jdbc:mysql://localhost:3306/db";
        try {
            //1获取连接
            Connection conn = DriverManager.getConnection(url, "root", "mysql");
            //2创建命令
            Statement stat = conn.createStatement();
            //3执行命令  execute执行任何SQL语句，不直接使用,executeUpdate() 执行增删改 executeQuery()执行查询
            //int result=stat.executeUpdate("create database `test01`");
            int result = stat.executeUpdate("create table Teacher(id int primary key,name varchar(20))");
            System.out.println(result);
            //4关闭
            stat.close();
            conn.close();
            // 返回0
            System.out.println("执行成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    @Test
    public void testInsert() {
        //1获取连接
        String url = "jdbc:mysql://localhost:3306/db";
        try {
            //1获取连接
            Connection conn = DriverManager.getConnection(url, "root", "mysql");
            //2创建命令
            Statement stat = conn.createStatement();
            //3执行命令  execute执行任何SQL语句，不直接使用,executeUpdate() 执行增删改 executeQuery()执行查询
            String s = "insert into stu(sid, sname, age, gender) values (100, 'Hard', 100, 'male');";
            int result1 = stat.executeUpdate(s);
            System.out.println(result1);
            //4关闭
            stat.close();
            conn.close();
            // 返回1
            System.out.println("执行成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    @Test
    public void testQuery() {
        //1获取连接
        String url = "jdbc:mysql://localhost:3306/db";
        try {
            //1获取连接
            Connection conn = DriverManager.getConnection(url, "root", "mysql");
            //2创建命令
            Statement stat = conn.createStatement();
            //3执行命令  execute执行任何SQL语句，不直接使用,executeUpdate() 执行增删改 executeQuery()执行查询
            ResultSet rs = stat.executeQuery("select * from stu;");
            while (rs.next()) {
                int age = rs.getInt("age");
                String sname = rs.getString("sname");
                System.out.println(sname + "--" + age);
            }
            //4关闭
            rs.close();
            stat.close();
            conn.close();
            System.out.println("执行成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}
